package com.bot.adms.dao.impl;

import com.bot.adms.dao.IApplyInfoDao;
import com.bot.adms.model.entity.ApplyInfo;
import com.bot.adms.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 投放申请持久层接口实现
 * 
 * @author FZhao
 * @version 1.0.1
 */
public class ApplyInfoDaoImpl implements IApplyInfoDao {
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;

    /**
     * 添加投放申请
     *
     * @param applyInfo 投放申请信息，包含：投放申请id,投放申请联系人名称等等
     * @return Integer 添加成功的数量
     */
    @Override
    public Integer insertApplyInfo(ApplyInfo applyInfo) {
        try {
            //1.创建连接
            conn = JdbcUtils.getConnection();
            //2.根据连接获取Statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String createTimeFmt = dateFormat.format(applyInfo.getCreateTime());
            String submitTimeFmt = applyInfo.getSubmitTime() != null ? dateFormat.format(applyInfo.getSubmitTime()) : null;

            String sql = "INSERT INTO `bo_adms`.`apply_info` (`project`, `link_man_name`, `tel`, `start_time`, `end_time`, `create_time`, `update_time`, `submit_time`, `status`) VALUES ('"
                    + applyInfo.getProject() + "', '" + applyInfo.getLinkManName() + "', '" + applyInfo.getTel() + "', '"
                    + dateFormat.format(applyInfo.getStartTime()) + "', '" + dateFormat.format(applyInfo.getEndTime()) + "', '" + createTimeFmt + "', '"
                    + dateFormat.format(applyInfo.getUpdateTime()) + "', " + submitTimeFmt + ", " + applyInfo.getStatus() + ");";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 修改投放申请
     *
     * @param applyInfo 投放申请信息，包含：投放申请id,投放申请联系人名称等等
     * @return Integer 修改成功的数量
     */
    @Override
    public Integer updateApplyInfo(ApplyInfo applyInfo) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sql = "UPDATE `bo_adms`.`apply_info` SET `project` = '" + applyInfo.getProject() + "', `link_man_name` = '" + applyInfo.getLinkManName() + "', `tel` = '" + applyInfo.getTel() + "', `start_time` = '"
                    + dateFormat.format(applyInfo.getStartTime()) + "', `end_time` = '" + dateFormat.format(applyInfo.getEndTime()) + "', `update_time` = '" + dateFormat.format(applyInfo.getUpdateTime()) + "'";
            if (applyInfo.getSubmitTime() != null) {
                sql += ", `submit_time` = '" + dateFormat.format(applyInfo.getSubmitTime()) + "'";
            }
            if (applyInfo.getStatus() != null) {
                sql += ", `status` = " + applyInfo.getStatus();
            }
            sql += " WHERE `id` = " + applyInfo.getId() + ";";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 删除投放申请
     *
     * @param id 投放申请id
     * @return Integer 删除成功的数量
     */
    @Override
    public Integer deleteApplyInfo(Long id) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "DELETE FROM `bo_adms`.`apply_info` WHERE `id` = " + id + ";";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 查找（获取）投放申请信息
     *
     * @param id 投放申请id
     * @return ApplyInfo 投放申请信息，包含：投放申请id,投放申请联系人名称等等
     */
    @Override
    public ApplyInfo getApplyInfoById(Long id) {
        ApplyInfo applyInfo = null;
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "SELECT * FROM `bo_adms`.`apply_info` WHERE `id` = " + id + ";";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                applyInfo = new ApplyInfo();
                applyInfo.setId(rs.getLong("id"));
                applyInfo.setProject(rs.getString("project"));
                applyInfo.setLinkManName(rs.getString("link_man_name"));
                applyInfo.setTel(rs.getString("tel"));
                applyInfo.setStartTime(rs.getTimestamp("start_time"));
                applyInfo.setEndTime(rs.getTimestamp("end_time"));
                applyInfo.setCreateTime(rs.getTimestamp("create_time"));
                applyInfo.setUpdateTime(rs.getTimestamp("update_time"));
                applyInfo.setSubmitTime(rs.getTimestamp("submit_time"));
                applyInfo.setStatus(rs.getInt("status"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return applyInfo;
    }

    /**
     * 查询投放申请信息
     *
     * @param applyInfo 投放申请信息，包含：投放申请id,投放申请联系人名称等等
     * @return List<ApplyInfo> 投放申请信息集合，包含：投放申请id,投放申请联系人名称等等
     */
    @Override
    public List<ApplyInfo> searchApplyInfo(ApplyInfo applyInfo) {
        List<ApplyInfo> applyInfoList = new ArrayList<>();
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sql = "SELECT * FROM `bo_adms`.`apply_info` WHERE 1=1";
            if (applyInfo.getProject() != null && !applyInfo.getProject().isEmpty()) {
                sql += " AND project LIKE '%" + applyInfo.getProject() + "%'";
            }
            if (applyInfo.getLinkManName() != null && !applyInfo.getLinkManName().isEmpty()) {
                sql += " AND link_man_name LIKE '%" + applyInfo.getLinkManName() + "%'";
            }
            if (applyInfo.getTel() != null && !applyInfo.getTel().isEmpty()) {
                sql += " AND tel LIKE '%" + applyInfo.getTel() + "%'";
            }
            if (applyInfo.getStartTime() != null) {
                sql += " AND start_time = '" + dateFormat.format(applyInfo.getStartTime()) + "'";
            }
            if (applyInfo.getEndTime() != null) {
                sql += " AND end_time = '" + dateFormat.format(applyInfo.getEndTime()) + "'";
            }
            if (applyInfo.getCreateTime() != null) {
                sql += " AND create_time = '" + dateFormat.format(applyInfo.getCreateTime()) + "'";
            }
            if (applyInfo.getStatus() != null) {
                sql += " AND status = '" + applyInfo.getStatus() + "'";
            }
            if (applyInfo.getId() != null) {
                sql += " AND id = " + applyInfo.getId();
            }
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                ApplyInfo info = new ApplyInfo();
                info.setId(rs.getLong("id"));
                info.setProject(rs.getString("project"));
                info.setLinkManName(rs.getString("link_man_name"));
                info.setTel(rs.getString("tel"));
                info.setStartTime(rs.getTimestamp("start_time"));
                info.setEndTime(rs.getTimestamp("end_time"));
                info.setCreateTime(rs.getTimestamp("create_time"));
                info.setUpdateTime(rs.getTimestamp("update_time"));
                info.setSubmitTime(rs.getTimestamp("submit_time"));
                info.setStatus(rs.getInt("status"));
                applyInfoList.add(info);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return applyInfoList;
    }
}
